#!/usr/bin/python
# -- coding:utf8 --

import os
import sys
import json
import pandas as pd
import requests

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
# import util_log as utils
from model_phl.utils import select_sql_mysql_v1

def get_raw_df(ApplyNO):
    # 第一步：获取URL链接
    file_url_sql = """
    select 
    ApplyNO
    ,FileUrl
    from BindingFileUrl
    where ApplyNO = '{}'
    """.format(ApplyNO)
    url_df = select_sql_mysql_v1(file_url_sql)

    # 第二步：获取数据集
    response = requests.get(url_df['FileUrl'][0])
    if response.status_code == 200:
        json_data = response.json()
    else:
        print(f"Failed to download {url_df['FileUrl'][0]}, status code: {response.status_code}")

    # 第三步：解析sms数据
    json_data1 = eval(json_data)
    try:
        # 第四步：从JSON格式获取数据集
        # 1）SMS数据和APP数据
        sms_df = pd.DataFrame(json.loads(json_data1['smss']))
        sms_df['ApplyNO'] = ApplyNO

        # 2）APP数据
        app_df = pd.DataFrame(json.loads(json_data1['apps']))
        app_df['ApplyNO'] = ApplyNO
    except Exception as e:
        print(f"请求失败，错误信息: {str(e)}")

    # 第四步：获取申请时间
    apply_date_sql = """
        select 
        ApplyNO
        ,ApplyDate
        from SysJkApply
        where ApplyNO = '{}'
        """.format(ApplyNO)
    applydate_df = select_sql_mysql_v1(apply_date_sql)

    # 第五步：数据合并
    sms_df = sms_df.merge(applydate_df)
    app_df = app_df.merge(applydate_df)

    return sms_df, app_df


if __name__ == '__main__':
    ApplyNO = '169873007701625291'
    sms_df, app_df = get_raw_df(ApplyNO)
    print(sms_df.shape)
    print(sms_df.columns)
    print(app_df.shape)
    print(app_df.columns)

